﻿using System;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Common;


namespace AccessControl.DataAcess
{
    public class GuestAccess
    {
        public static AccessControlDS.tblGuestDataTable SelectAll()
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var tbl = new AccessControlDS.tblGuestDataTable();

                var cmdSelect = new SqlCommand("SELECT * FROM tblGuest", cn);
                try
                {
                    cn.Open();
                    var reader = cmdSelect.ExecuteReader();
                    while (reader.Read())
                    {
                        var newRow = tbl.NewtblGuestRow();
                        newRow.pkGuest = (Int64)reader["pkGuest"];
                        newRow.fkPerson = (String)reader["fkPerson"];
                        newRow.GuestFirstName = (String)reader["GuestFirstName"];
                        newRow.GuestLastName = (String)reader["GuestLastName"];
                        newRow.Description = (String)reader["Description"];
                       
                        tbl.AddtblGuestRow(newRow);
                    }
                }
                finally
                {
                    cn.Close();
                }
                return tbl.Rows.Count != 0 ? tbl : null;
            }
        }

        public static AccessControlDS.tblGuestDataTable SelectByFilter(string filter)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand(String.Format("SELECT * FROM tblGuest WHERE {0}", filter), cn);
                var tbl = new AccessControlDS.tblGuestDataTable();

                try
                {
                    cn.Open();
                    var reader = cmdSelect.ExecuteReader();
                    while (reader.Read())
                    {
                        var newRow = tbl.NewtblGuestRow();
                        newRow.pkGuest = (Int64)reader["pkGuest"];
                        newRow.fkPerson = (String)reader["fkPerson"];
                        newRow.GuestFirstName = (String)reader["GuestFirstName"];
                        newRow.GuestLastName = (String)reader["GuestLastName"];
                        newRow.Description = (String)reader["Description"];
                        tbl.AddtblGuestRow(newRow);
                    }
                }
                finally
                {
                    cn.Close();
                }
                return tbl.Rows.Count != 0 ? tbl : null;
            }
        }

        public static void Insert(AccessControlDS.tblGuestRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdInsert = new SqlCommand("spGuest_Insert", cn) { CommandType = CommandType.StoredProcedure };
                cmdInsert.Parameters.AddWithValue("@fkPerson", row.fkPerson);
                cmdInsert.Parameters.AddWithValue("@GuestFirstName", row.GuestFirstName);
                cmdInsert.Parameters.AddWithValue("@GuestLastName", row.GuestLastName);
                cmdInsert.Parameters.AddWithValue("@Description", row.Description);
                try
                {
                    cn.Open();
                    cmdInsert.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public static void Update(AccessControlDS.tblGuestRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdUpdate = new SqlCommand("spEvent_Update", cn) { CommandType = CommandType.StoredProcedure };
                cmdUpdate.Parameters.AddWithValue("@pkEpkGuestvent", row.pkGuest);
                cmdUpdate.Parameters.AddWithValue("@fkPerson", row.fkPerson);
                cmdUpdate.Parameters.AddWithValue("@GuestFirstName", row.GuestFirstName);
                cmdUpdate.Parameters.AddWithValue("@GuestLastName", row.GuestLastName);
                cmdUpdate.Parameters.AddWithValue("@Description", row.Description);
                try
                {
                    cn.Open();
                    cmdUpdate.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }
                                                 
        public static void Delete(AccessControlDS.tblGuestRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdDelete = new SqlCommand("spEvent_Delete", cn) { CommandType = CommandType.StoredProcedure };
                cmdDelete.Parameters.AddWithValue("@pkGuest", row.pkGuest);
                try
                {
                    cn.Open();
                    cmdDelete.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }
    }
}
